using Dapper;
using MediatR;
using RBAC.ErrorCode;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;
using RBAC.Read.Api.Application.Dto.ERP.SalesManagement.H;
using RBAC.Interstructrue.Base;
using static RBAC.ErrorCode.APIEnums;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 获取发货通知单明细列表处理器
    /// </summary>
    public class GetDeliveryNoticeItemListHandler : IRequestHandler<GetDeliveryNoticeItemListCommand, APIResult<GetDeliveryNoticeItemListDto>>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public GetDeliveryNoticeItemListHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<APIResult<GetDeliveryNoticeItemListDto>> Handle(GetDeliveryNoticeItemListCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var connection = _dbConnectionFactory.CreateConnection();
                
                // 构建查询条件
                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();
                
                // 基础条件：只查询未删除的记录
                whereConditions.Add("dni.IsDeleted = 0");
                
                // 发货通知单ID（可选）
                if (request.DeliveryNoticeId.HasValue)
                {
                    whereConditions.Add("dni.Delivery_notice_id = @DeliveryNoticeId");
                    parameters.Add("@DeliveryNoticeId", request.DeliveryNoticeId.Value);
                }
                
                if (!string.IsNullOrEmpty(request.MaterialCode))
                {
                    whereConditions.Add("dni.Material_code LIKE @MaterialCode");
                    parameters.Add("@MaterialCode", $"%{request.MaterialCode}%");
                }
                
                if (!string.IsNullOrEmpty(request.MaterialName))
                {
                    whereConditions.Add("dni.Material_name LIKE @MaterialName");
                    parameters.Add("@MaterialName", $"%{request.MaterialName}%");
                }

                var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // 查询总数
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM Delivery_notice_item dni
                    {whereClause}";

                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                // 分页查询数据
                var offset = (request.PageIndex - 1) * request.PageSize;
                var dataSql = $@"
                    SELECT 
                        (@row_number:=@row_number + 1) as SerialNumber,
                        dni.Id,
                        dni.Delivery_notice_id as DeliveryNoticeId,
                        dni.Material_id as MaterialId,
                        dni.Material_code as MaterialCode,
                        dni.Material_name as MaterialName,
                        dni.Unit,
                        dni.Qty,
                        dni.Package_qty as PackageQty,
                        dni.Batch_no as BatchNo,
                        dni.Remark,
                        dni.CreateTime,
                        dni.CreateUser,
                        '查看 编辑 删除' as Operation
                    FROM Delivery_notice_item dni
                    CROSS JOIN (SELECT @row_number := {offset}) as r
                    {whereClause}
                    ORDER BY dni.Id ASC
                    LIMIT @PageSize OFFSET @Offset";

                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", request.PageSize);

                var items = await connection.QueryAsync<DeliveryNoticeItemDto>(dataSql, parameters);

                var result = new GetDeliveryNoticeItemListDto
                {
                    TotalCount = totalCount,
                    Items = items.ToList()
                };

                return new APIResult<GetDeliveryNoticeItemListDto>
                {
                    Code = Success,
                    Msg = "查询成功",
                    Data = result
                };
            }
            catch (Exception ex)
            {
                return new APIResult<GetDeliveryNoticeItemListDto>
                {
                    Code = Error,
                    Msg = $"查询发货通知单明细列表失败: {ex.Message}",
                    Data = default
                };
            }
        }
    }
} 